const pool = require('../config/database');

class VaccinationHistoryModel {
  /**
   * 创建预防接种史记录
   * @param {Object} vaccinationData - 预防接种史数据
   * @returns {Promise<Object>} 创建的预防接种史记录
   */
  static async create(vaccinationData) {
    const connection = await pool.getConnection();
    
    try {
      await connection.beginTransaction();
      
      const fields = Object.keys(vaccinationData);
      const values = Object.values(vaccinationData);
      const placeholders = fields.map(() => '?').join(', ');
      
      const sql = `
        INSERT INTO jktj_vaccination_history (${fields.join(', ')})
        VALUES (${placeholders})
      `;
      
      const [result] = await connection.execute(sql, values);
      
      await connection.commit();
      
      return {
        id: result.insertId,
        ...vaccinationData
      };
    } catch (error) {
      await connection.rollback();
      throw error;
    } finally {
      connection.release();
    }
  }

  /**
   * 批量创建预防接种史记录
   * @param {Array<Object>} vaccinationList - 预防接种史数据数组
   * @returns {Promise<Array>} 创建的预防接种史记录数组
   */
  static async createBatch(vaccinationList) {
    if (!vaccinationList || vaccinationList.length === 0) {
      return [];
    }

    const connection = await pool.getConnection();
    const results = [];
    
    try {
      await connection.beginTransaction();
      
      for (const vaccinationData of vaccinationList) {
        const fields = Object.keys(vaccinationData);
        const values = Object.values(vaccinationData);
        const placeholders = fields.map(() => '?').join(', ');
        
        const sql = `
          INSERT INTO jktj_vaccination_history (${fields.join(', ')})
          VALUES (${placeholders})
        `;
        
        const [result] = await connection.execute(sql, values);
        
        results.push({
          id: result.insertId,
          ...vaccinationData
        });
      }
      
      await connection.commit();
      return results;
    } catch (error) {
      await connection.rollback();
      throw error;
    } finally {
      connection.release();
    }
  }

  /**
   * 根据体检ID获取预防接种史记录列表
   * @param {number} tjId - 体检ID
   * @returns {Promise<Array>} 预防接种史记录列表
   */
  static async findByTjId(tjId) {
    const sql = 'SELECT * FROM jktj_vaccination_history WHERE tj_id = ? ORDER BY jzrq DESC';
    const [rows] = await pool.execute(sql, [tjId]);
    return rows;
  }

  /**
   * 根据ID获取预防接种史记录
   * @param {number} id - 预防接种史记录ID
   * @returns {Promise<Object>} 预防接种史记录
   */
  static async findById(id) {
    const sql = 'SELECT * FROM jktj_vaccination_history WHERE id = ?';
    const [rows] = await pool.execute(sql, [id]);
    return rows[0] || null;
  }

  /**
   * 更新预防接种史记录
   * @param {number} id - 预防接种史记录ID
   * @param {Object} updateData - 更新数据
   * @returns {Promise<boolean>} 更新结果
   */
  static async update(id, updateData) {
    const fields = Object.keys(updateData);
    const values = Object.values(updateData);
    const setClause = fields.map(field => `${field} = ?`).join(', ');
    
    const sql = `UPDATE jktj_vaccination_history SET ${setClause} WHERE id = ?`;
    const [result] = await pool.execute(sql, [...values, id]);
    
    return result.affectedRows > 0;
  }

  /**
   * 删除预防接种史记录
   * @param {number} id - 预防接种史记录ID
   * @returns {Promise<boolean>} 删除结果
   */
  static async delete(id) {
    const sql = 'DELETE FROM jktj_vaccination_history WHERE id = ?';
    const [result] = await pool.execute(sql, [id]);
    return result.affectedRows > 0;
  }

  /**
   * 根据体检ID删除所有预防接种史记录
   * @param {number} tjId - 体检ID
   * @returns {Promise<boolean>} 删除结果
   */
  static async deleteByTjId(tjId) {
    const sql = 'DELETE FROM jktj_vaccination_history WHERE tj_id = ?';
    const [result] = await pool.execute(sql, [tjId]);
    return result.affectedRows > 0;
  }

  /**
   * 批量获取多个体检的预防接种史记录
   * @param {Array<number>} tjIds - 体检ID数组
   * @returns {Promise<Array>} 预防接种史记录数组
   */
  static async findByTjIds(tjIds) {
    if (!tjIds || tjIds.length === 0) {
      return [];
    }
    
    const placeholders = tjIds.map(() => '?').join(', ');
    const sql = `
      SELECT * FROM jktj_vaccination_history 
      WHERE tj_id IN (${placeholders}) 
      ORDER BY tj_id, jzrq DESC
    `;
    const [rows] = await pool.execute(sql, tjIds);
    return rows;
  }

  /**
   * 根据疫苗名称搜索接种记录
   * @param {number} tjId - 体检ID
   * @param {string} vaccineName - 疫苗名称
   * @returns {Promise<Array>} 预防接种史记录列表
   */
  static async findByVaccineName(tjId, vaccineName) {
    const sql = `
      SELECT * FROM jktj_vaccination_history 
      WHERE tj_id = ? AND ymmc LIKE ?
      ORDER BY jzrq DESC
    `;
    const [rows] = await pool.execute(sql, [tjId, `%${vaccineName}%`]);
    return rows;
  }

  /**
   * 获取指定时间范围内的接种记录
   * @param {number} tjId - 体检ID
   * @param {Date} startDate - 开始日期
   * @param {Date} endDate - 结束日期
   * @returns {Promise<Array>} 预防接种史记录列表
   */
  static async findByDateRange(tjId, startDate, endDate) {
    const sql = `
      SELECT * FROM jktj_vaccination_history 
      WHERE tj_id = ? AND jzrq >= ? AND jzrq <= ?
      ORDER BY jzrq DESC
    `;
    const [rows] = await pool.execute(sql, [tjId, startDate, endDate]);
    return rows;
  }

  /**
   * 获取疫苗接种统计
   * @param {number} tjId - 体检ID
   * @returns {Promise<Object>} 接种统计
   */
  static async getVaccinationStats(tjId) {
    const sql = `
      SELECT 
        ymmc,
        COUNT(*) as count,
        MAX(jzrq) as last_vaccination_date
      FROM jktj_vaccination_history 
      WHERE tj_id = ?
      GROUP BY ymmc
      ORDER BY count DESC
    `;
    const [rows] = await pool.execute(sql, [tjId]);
    
    const totalSql = 'SELECT COUNT(*) as total FROM jktj_vaccination_history WHERE tj_id = ?';
    const [totalRows] = await pool.execute(totalSql, [tjId]);
    
    return {
      total: totalRows[0].total,
      vaccines: rows
    };
  }

  /**
   * 检查疫苗是否需要补种
   * @param {Object} vaccinationData - 接种记录数据
   * @param {number} validityYears - 疫苗有效期（年）
   * @returns {boolean} 是否需要补种
   */
  static needsRevaccination(vaccinationData, validityYears = 10) {
    if (!vaccinationData.jzrq) {
      return true;
    }
    
    const vaccinationDate = new Date(vaccinationData.jzrq);
    const currentDate = new Date();
    const yearsDiff = (currentDate - vaccinationDate) / (1000 * 60 * 60 * 24 * 365);
    
    return yearsDiff > validityYears;
  }

  /**
   * 获取常见疫苗字典
   * @returns {Array} 常见疫苗列表
   */
  static getCommonVaccines() {
    return [
      '乙肝疫苗',
      '流感疫苗',
      '肺炎疫苗',
      '带状疱疹疫苗',
      'HPV疫苗',
      '破伤风疫苗',
      '狂犬疫苗',
      '水痘疫苗',
      '麻疹疫苗',
      '风疹疫苗',
      '腮腺炎疫苗',
      '脊髓灰质炎疫苗',
      '百白破疫苗',
      'BCG疫苗'
    ];
  }
}

module.exports = VaccinationHistoryModel;